package com.zmh.common;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSON;
import com.zmh.dao.BookDao;
import com.zmh.model.Column;
import com.zmh.model.book.BookInfo;
import com.zmh.model.book.UpdWebsit;

/**
 * @author zhoumh
 * @version 创建时间：2017年12月19日 下午5:13:11
 * @Description
 */
@Service
public class GenMysqlUtil {
	private static final Logger LOGGER = LoggerFactory.getLogger(GenMysqlUtil.class);
	//声明jdbcTemplate 在注入的时候声明DataSource
	private JdbcTemplate jdbcTemplate;
	@Autowired
    public void setHrjdbcTemplate(@Qualifier("dataSourceMeta") DataSource dataSource) {
		 //根据配置文件中的DataSource名称使用数据源
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
	private static final String entityPac = "com.zmh.model";
	private static final String daoPac = "com.zmh.dao";
	private static final String servicePac = "com.zmh.service";
	private static final String authorName = "zhoumh";
    private List<Column> columns; //列属性

	/**
	 * 生成实体类
	 * @param name TableName 使用大驼峰命名法
	 * @return: void
	 * @author: zhoumh
	 * @version: 创建时间：2017年12月21日 下午2:34:42 
	 * @Description: 传入的表名涉及到创建类名称，因此对表名要严格按照大驼峰命名法
	 */
	public void genEntity(String tableName){
		columns = getColumns(StringUtils.lowerCase(tableName));
		StringBuffer sb = new StringBuffer();
		sb.append("package " + entityPac + ";\r\n");  
		sb.append("\r\n");  
		//注释部分  
		sb.append("/**\r\n");  
		sb.append(" * "+tableName+" 实体类\r\n");  
		sb.append(" * @author "+authorName+"\r\n");  
		sb.append(" * @version  创建时间："+DateUtil.getDateTime()+"\r\n");  
		sb.append(" * @Description  \r\n"); 
		sb.append(" */ \r\n");  
		//实体部分  
		sb.append("\r\npublic class " + StringUtils.capitalize(tableName) + "{\r\n"); 
		// 生成属性
		procAttrs(sb);
		// 生成get/set方法
		procGetAndSet(sb);
        sb.append("}\r\n");  
        // 输出到java文件
        printFile(sb,tableName,entityPac);
        System.out.println(sb.toString());
	}
	
	public void genDao(String tableName){
		columns = getColumns(StringUtils.lowerCase(tableName));
		StringBuffer sb = new StringBuffer();
		sb.append("package " + daoPac + ";\r\n");  
		sb.append("import java.sql.ResultSet;\r\n");  
		sb.append("import java.sql.SQLException;\r\n");  
		sb.append("import java.util.ArrayList;\r\n");  
		sb.append("import java.util.List;\r\n");  
		sb.append("\r\n"); 
		sb.append("import org.slf4j.Logger;\r\n");  
		sb.append("import org.slf4j.LoggerFactory;\r\n");  
		sb.append("import org.springframework.beans.factory.annotation.Autowired;\r\n");  
		sb.append("import org.springframework.dao.EmptyResultDataAccessException;\r\n");  
		sb.append("import org.springframework.jdbc.core.JdbcTemplate;\r\n");  
		sb.append("import org.springframework.jdbc.core.RowMapper;\r\n");  
		sb.append("import org.springframework.stereotype.Repository;\r\n");  
		sb.append("\r\n");  
		//注释部分  
		sb.append("/**\r\n");  
		sb.append(" * "+tableName+" 数据操作\r\n");  
		sb.append(" * @author "+authorName+"\r\n"); 
		sb.append(" * @version  创建时间："+DateUtil.getDateTime()+"\r\n");
		sb.append(" * @Description  \r\n"); 
		sb.append(" */ \r\n");  
		sb.append("\r\n@Repository\r\n");  
		//实体部分  
		sb.append("public class " + StringUtils.capitalize(tableName) + "Dao{\r\n"); 
		//log日志
		sb.append("\r\n\tprivate static final Logger LOGGER = LoggerFactory.getLogger("+StringUtils.capitalize(tableName)+"Dao.class);\r\n");
		sb.append("\t@Autowired \r\n");  
		sb.append("\tprivate JdbcTemplate jdbcTemplate; \r\n");  
		// 生成方法，包括查询、修改、删除、增加
		procSelAll(sb,tableName);
		procSelByUuid(sb,tableName);
		procSumByState(sb,tableName);
		procIns(sb,tableName);
		procUpdByUuid(sb,tableName);
		procDelByUuid(sb,tableName);
		//生成结果集映射方法
		procObjSet(sb,tableName);
        sb.append("}\r\n");  
        // 输出到java文件
        printFile(sb,tableName+"Dao",daoPac);
        System.out.println(sb.toString());
	}
	
	public void genService(String tableName){
		columns = getColumns(StringUtils.lowerCase(tableName));
		StringBuffer sb = new StringBuffer();
		sb.append("package " + servicePac + ";\r\n");
		sb.append("import org.slf4j.Logger;\r\n");  
		sb.append("import org.slf4j.LoggerFactory;\r\n");  
		sb.append("import org.springframework.beans.factory.annotation.Autowired;\r\n");  
		sb.append("\r\n");  
		//注释部分  
		sb.append("/**\r\n");  
		sb.append(" * "+tableName+" 服务\r\n");  
		sb.append(" * @author "+authorName+"\r\n"); 
		sb.append(" * @version  创建时间："+DateUtil.getDateTime()+"\r\n");
		sb.append(" * @Description  \r\n"); 
		sb.append(" */ \r\n");  
		sb.append("\r\n@Service\r\n");  
		//实体部分  
		sb.append("public class " + StringUtils.capitalize(tableName) + "Service{\r\n"); 
		//log日志
		sb.append("\r\n\tprivate static final Logger LOGGER = LoggerFactory.getLogger("+StringUtils.capitalize(tableName)+"Service.class);\r\n");
		sb.append("\t@Autowired \r\n"); 
		sb.append("\tprivate "+StringUtils.capitalize(tableName)+"Dao "+tableName+"Dao; \r\n"); 
		
        sb.append("}\r\n");  
        // 输出到java文件
        printFile(sb,tableName+"Service",servicePac);
        System.out.println(sb.toString());
	}
	/**
	 * 生成属性信息
	 * @param sb 
	 * @author: zhoumh
	 * @version: 创建时间：2017年12月22日 上午11:15:17 
	 * @Description:
	 */
    private void procAttrs(StringBuffer sb) {  
        for (Column column : columns) {
        	//注释
        	sb.append("\t// "+column.getCOLUMN_COMMENT()+"\r\n");  
        	//属性
        	sb.append("\tprivate " + sqlType2JavaType(column.getDATA_TYPE()) + " " + column.getCOLUMN_NAME() + ";\r\n");  
		}
    } 
    
    /**
     * 功能：生成get/set方法
     * @param sb 
     * @author: zhoumh
     * @version: 创建时间：2017年12月22日 上午11:16:46 
     * @Description:
     */
    private void procGetAndSet(StringBuffer sb) {  
    	for (Column column : columns) {
    		//注释
        	sb.append("\t/** "+column.getCOLUMN_COMMENT()+" */\r\n"); 
        	//set方法
            sb.append("\tpublic void set" + StringUtils.capitalize(column.getCOLUMN_NAME()) + "(" + sqlType2JavaType(column.getDATA_TYPE()) + " " +   
            		column.getCOLUMN_NAME() + "){\r\n");  
            sb.append("\t\tthis." + column.getCOLUMN_NAME() + "=" + column.getCOLUMN_NAME() + ";\r\n");  
            sb.append("\t}\r\n");  
            //注释
            sb.append("\t/** "+column.getCOLUMN_COMMENT()+" */\r\n"); 
        	//get方法
            sb.append("\tpublic " + sqlType2JavaType(column.getDATA_TYPE()) + " get" + StringUtils.capitalize(column.getCOLUMN_NAME()) + "(){\r\n");  
            sb.append("\t\treturn " + column.getCOLUMN_NAME() + ";\r\n");  
            sb.append("\t}\r\n");  
        }  
          
    } 
    /**
     * 创建查询方法
     * @author zhoumh
     * @version 创建时间：2017年12月24日 下午9:10:11
     */
    private void procSelAll(StringBuffer sb,String tableName){
    	// 首字母大写的实体类名
    	String tableModel = StringUtils.capitalize(tableName);
    	// 实体对象的list集合
    	String listModel = "List<"+tableModel+">";
		// 注释部分  
		sb.append("\t/**\r\n");  
		sb.append("\t * 查询全部信息\r\n");  
		sb.append("\t * @return List<"+tableModel+">\r\n"); 
		sb.append("\t * @author "+authorName+"\r\n"); 
		sb.append("\t * @version  创建时间："+DateUtil.getDateTime()+"\r\n");
		sb.append("\t * @Description  \r\n"); 
		sb.append("\t */ \r\n");  
		// public List<UpdWebsit> selWebsitByAll(){
		sb.append("\tpublic "+listModel+" sel" + tableModel + "ByAll(){\r\n"); 
		String sql = "String sqlstr = \"SELECT";
		for (Column column : columns) {
			sql=sql+" "+column.getCOLUMN_NAME()+",";
		}
		sql = sql.substring(0, sql.length()-1);
		sb.append("\t\t"+sql+" FROM "+tableName+"\";\r\n");
//		List<UpdWebsit> updWebsits = new ArrayList<>();
		sb.append("\t\t"+listModel+" "+tableName+"s = new ArrayList<>();\r\n");
		sb.append("\t\ttry { \r\n");
		sb.append("\t\t\t"+tableName+"s = jdbcTemplate.query(sqlstr, new RowMapper<" + tableModel + ">(){\r\n");
		sb.append("\t\t\t\t@Override\r\n");
		sb.append("\t\t\t\tpublic "+tableModel+" mapRow(ResultSet rs, int rowNum) throws SQLException {\r\n");
		sb.append("\t\t\t\t\treturn objSet(rs, rowNum); \r\n");
		sb.append("\t\t\t\t} \r\n");
		sb.append("\t\t\t\t}); \r\n");
		sb.append("\t\t} catch (EmptyResultDataAccessException e) { \r\n");
		sb.append("\t\t\te.printStackTrace(); \r\n");
		sb.append("\t\t} \r\n");
		sb.append("\t\treturn "+tableName+"s; \r\n");
		sb.append("\t}\r\n");  
    }
    /**
     * 创建查询方法
     * @author zhoumh
     * @version 创建时间：2017年12月25日 下午2:20:53 
     */
    private void procSelByUuid(StringBuffer sb,String tableName){
    	// 首字母大写的实体类名
    	String tableModel = StringUtils.capitalize(tableName);
		// 注释部分  
		sb.append("\t/**\r\n");  
		sb.append("\t * 根据UUID查询信息\r\n");  
		sb.append("\t * @param uuid\r\n");  
		sb.append("\t * @return "+tableModel+"\r\n"); 
		sb.append("\t * @author "+authorName+"\r\n"); 
		sb.append("\t * @version  创建时间："+DateUtil.getDateTime()+"\r\n");
		sb.append("\t * @Description  \r\n"); 
		sb.append("\t */ \r\n");  
		// public List<UpdWebsit> selWebsitByAll(){
		sb.append("\tpublic "+tableModel+" sel" + tableModel + "ByAll(String uuid){\r\n"); 
		String sql = "String sqlstr = \"SELECT";
		for (Column column : columns) {
			sql=sql+" "+column.getCOLUMN_NAME()+",";
		}
		sql = sql.substring(0, sql.length()-1);
		sb.append("\t\t"+sql+" FROM "+tableName+" WHERE uuid=?\";\r\n");
//		UpdWebsit updWebsit = new ArrayList<>();
		sb.append("\t\t"+tableModel+" "+tableName+" = new "+tableModel+"();\r\n");
		sb.append("\t\tObject[] param = new Object[]{uuid};\r\n");
		sb.append("\t\ttry { \r\n");
		sb.append("\t\t\t"+tableName+" = jdbcTemplate.queryForObject(sqlstr, param, new RowMapper<" + tableModel + ">(){\r\n");
		sb.append("\t\t\t\t@Override\r\n");
		sb.append("\t\t\t\tpublic "+tableModel+" mapRow(ResultSet rs, int rowNum) throws SQLException {\r\n");
		sb.append("\t\t\t\t\treturn objSet(rs, rowNum); \r\n");
		sb.append("\t\t\t\t} \r\n");
		sb.append("\t\t\t\t}); \r\n");
		sb.append("\t\t} catch (EmptyResultDataAccessException e) { \r\n");
		sb.append("\t\t\te.printStackTrace(); \r\n");
		sb.append("\t\t} \r\n");
		sb.append("\t\treturn "+tableName+"; \r\n");
		sb.append("\t}\r\n");  
    }
    /**
     * 创建根据状态统计总数方法
     * @author zhoumh
     * @version 创建时间：2017年12月25日 下午2:20:53 
     */
    private void procSumByState(StringBuffer sb,String tableName){
		sb.append("\t/**\r\n");  
		sb.append("\t * 根据状态查询总条数\r\n");  
		sb.append("\t * @param state\r\n"); 
		sb.append("\t * @return int\r\n"); 
		sb.append("\t * @author "+authorName+"\r\n"); 
		sb.append("\t * @version  创建时间："+DateUtil.getDateTime()+"\r\n");
		sb.append("\t * @Description  \r\n"); 
		sb.append("\t */ \r\n");  
		sb.append("\tpublic int sumByState(String state){\r\n"); 
		String sql = "String sqlstr = \"  SELECT count(id) FROM "+tableName+" WHERE state = ?\";";
		sb.append("\t\t"+sql+"\r\n");
		sb.append("\t\tObject[] param = new Object[]{state};\r\n");
		sb.append("\t\tint count = jdbcTemplate.queryForObject(sqlstr,param,Integer.class);\r\n");
		sb.append("\t\treturn count;\r\n");
		sb.append("\t}\r\n");  
    }
    /**
     * 创建数据插入方法
     * @return: void
     * @author: zhoumh
     * @version: 创建时间：2017年12月25日 下午2:20:53 
     * @Description:
     */
    private void procIns(StringBuffer sb,String tableName){
    	// 首字母大写的实体类名
    	String tableModel = StringUtils.capitalize(tableName);
		sb.append("\t/**\r\n");  
		sb.append("\t * 插入数据\r\n");  
		sb.append("\t * @param state\r\n"); 
		sb.append("\t * @return int\r\n"); 
		sb.append("\t * @author "+authorName+"\r\n"); 
		sb.append("\t * @version  创建时间："+DateUtil.getDateTime()+"\r\n");
		sb.append("\t * @Description  \r\n"); 
		sb.append("\t */ \r\n");  
		sb.append("\tpublic int ins"+tableModel+"("+tableModel+" "+tableName+"){\r\n"); 
		String sql = "String sqlstr = \"insert into "+tableName+" (";
		String value = ") values(";
		String param = "";
		for (Column column : columns) {
			if (("PRI".equals(column.getCOLUMN_KEY())&&"auto_increment".equals(column.getEXTRA()))||("datetime".equals(column.getCOLUMN_TYPE())&&"CURRENT_TIMESTAMP".equals(column.getCOLUMN_DEFAULT()))) {
				// 插入数据跳过自增主键、默认设置的时间
				continue;
			}
			sql=sql+" "+column.getCOLUMN_NAME()+",";
			value = value +"?,";
			param = param+tableName+".get"+StringUtils.capitalize(column.getCOLUMN_NAME())+"(),";
		}
		sql = sql.substring(0, sql.length()-1);
		value = value.substring(0, value.length()-1);
		param = param.substring(0, param.length()-1);
		sb.append("\t\t"+sql+value+")\";\r\n");
		sb.append("\t\tObject[] param = new Object[]{"+param+"};\r\n");
		sb.append("\t\tint result = jdbcTemplate.update(sqlstr, param);\r\n");
		sb.append("\t\treturn result;\r\n");
		sb.append("\t}\r\n");  
    }
    /**
     * 创建根据UUID修改数据方法
     * @author zhoumh
     * @version 创建时间：2017年12月24日 下午9:10:11
     */
    private void procUpdByUuid(StringBuffer sb,String tableName){
    	// 首字母大写的实体类名
    	String tableModel = StringUtils.capitalize(tableName);
		// 注释部分  
		sb.append("\t/**\r\n");  
		sb.append("\t * 根据UUID修改信息\r\n");  
		sb.append("\t * @param uuid\r\n");
		sb.append("\t * @return "+tableModel+"\r\n"); 
		sb.append("\t * @author "+authorName+"\r\n"); 
		sb.append("\t * @version  创建时间："+DateUtil.getDateTime()+"\r\n");
		sb.append("\t * @Description  \r\n"); 
		sb.append("\t */ \r\n");  
		// public List<UpdWebsit> selWebsitByAll(){
		sb.append("\tpublic int upd" + tableModel + "ByUuid("+tableModel+" "+tableName+"){\r\n"); 
		String sql = "String sqlstr = \"update "+tableName+" set ";
		String param = "";
		for (Column column : columns) {
			if ("PRI".equals(column.getCOLUMN_KEY())&&"auto_increment".equals(column.getEXTRA())) {
				// 更新数据跳过自增主键
				continue;
			}
			sql=sql+" "+column.getCOLUMN_NAME()+"=?,";
			param = param+tableName+".get"+StringUtils.capitalize(column.getCOLUMN_NAME())+"(),";
		}
		sql = sql.substring(0, sql.length()-1);
		sb.append("\t\t"+sql+" WHERE uuid=?\";\r\n");
//		UpdWebsit updWebsit = new ArrayList<>();
		sb.append("\t\tObject[] param = new Object[]{"+param+" "+tableName+".getUuid()};\r\n");
		sb.append("\t\tint result = jdbcTemplate.update(sqlstr, param);\r\n");
		sb.append("\t\treturn result; \r\n");
		sb.append("\t}\r\n");  
    }
    /**
     * 创建根据uuid删除数据方法
     * @author zhoumh
     * @version 创建时间：2017年12月25日 下午2:20:53 
     */
    private void procDelByUuid(StringBuffer sb,String tableName){
    	// 首字母大写的实体类名
    	String tableModel = StringUtils.capitalize(tableName);
		sb.append("\t/**\r\n");  
		sb.append("\t * 根据uuid删除书籍\r\n");  
		sb.append("\t * @param uuid\r\n"); 
		sb.append("\t * @return int\r\n"); 
		sb.append("\t * @author "+authorName+"\r\n"); 
		sb.append("\t * @version  创建时间："+DateUtil.getDateTime()+"\r\n");
		sb.append("\t * @Description  \r\n"); 
		sb.append("\t */ \r\n");  
		sb.append("\tpublic int del" + tableModel + "ByUuid(String uuid){\r\n"); 
		String sql = "String sqlstr = \"  delete from "+tableName+" where uuid = ? \";";
		sb.append("\t\t"+sql+"\r\n");
		sb.append("\t\tObject[] param = new Object[]{uuid};\r\n");
		sb.append("\t\tint result = jdbcTemplate.queryForObject(sqlstr,param,Integer.class);\r\n");
		sb.append("\t\treturn result;\r\n");
		sb.append("\t}\r\n");  
    }
    /**
     * 生成结果集映射方法
     * @author zhoumh
     * @version 创建时间：2017年12月24日 下午5:20:08
     */
    private void procObjSet(StringBuffer sb,String tableName){
    	String tableModel = StringUtils.capitalize(tableName);
		//注释部分  
		sb.append("\t/**\r\n");  
		sb.append("\t * 结果集映射\r\n");  
		sb.append("\t * @author "+authorName+"\r\n"); 
		sb.append("\t * @version  创建时间："+DateUtil.getDateTime()+"\r\n");
		sb.append("\t * @Description  \r\n"); 
		sb.append("\t */ \r\n");  
		// private BookInfo objSet(ResultSet rs, int rowNum) throws SQLException {
		sb.append("\tprivate " + tableModel + " objSet(ResultSet rs, int rowNum) throws SQLException{\r\n"); 
		// BookInfo book = new BookInfo();
		sb.append("\t\t"+tableModel+" "+tableName+" = new "+tableModel+"();\r\n");
		for (Column column : columns) {
			//book.setId(rs.getInt("id"));
			sb.append("\t\t" + tableName + ".set" + StringUtils.capitalize(column.getCOLUMN_NAME())+"(rs.get" + StringUtils.capitalize(sqlType2JavaType(column.getDATA_TYPE())) + "(\""+column.getCOLUMN_NAME()+"\"));\r\n");
		}
		sb.append("\t\treturn "+tableName+";\r\n");
		sb.append("\t};\r\n");

    }
    /**
     * 生成java文件
     * @param sb
     * @param tableName 
     * @return: void
     * @author: zhoumh
     * @version: 创建时间：2017年12月22日 下午5:13:02 
     * @Description:
     */
    private void printFile(StringBuffer sb,String tableName,String pac){
    	try {  
    		String content = sb.toString();
    		File directory = new File("");  
    		String outputPath = directory.getAbsolutePath()+ "/src/main/java/"+pac.replace(".", "/")+"/"+StringUtils.capitalize(tableName) + ".java";  
    		FileWriter fw = new FileWriter(outputPath);  
    		PrintWriter pw = new PrintWriter(fw);  
    		pw.println(content);  
    		pw.flush();  
    		pw.close();  
    	} catch (IOException e) {  
    		e.printStackTrace();  
    	} 
    }
	/**
	 * 将SQL数据类型转换成JAVA数据类型
	 * @param String sqlType
	 * @return: String javaType
	 * @author: zhoumh
	 * @version: 创建时间：2017年12月22日 上午10:53:34 
	 * @Description:
	 */
    private String sqlType2JavaType(String sqlType) {  
        
        if(sqlType.equalsIgnoreCase("bit")){  
            return "boolean";  
        }else if(sqlType.equalsIgnoreCase("tinyint")){  
            return "byte";  
        }else if(sqlType.equalsIgnoreCase("smallint")){  
            return "short";  
        }else if(sqlType.equalsIgnoreCase("int")){  
            return "int";  
        }else if(sqlType.equalsIgnoreCase("bigint")){  
            return "long";  
        }else if(sqlType.equalsIgnoreCase("float")){  
            return "float";  
        }else if(sqlType.equalsIgnoreCase("decimal") || sqlType.equalsIgnoreCase("numeric")   
                || sqlType.equalsIgnoreCase("real") || sqlType.equalsIgnoreCase("money")   
                || sqlType.equalsIgnoreCase("smallmoney")){  
            return "double";  
        }else if(sqlType.equalsIgnoreCase("varchar") || sqlType.equalsIgnoreCase("char")   
                || sqlType.equalsIgnoreCase("nvarchar") || sqlType.equalsIgnoreCase("nchar")   
                || sqlType.equalsIgnoreCase("text")){  
            return "String";  
        }else if(sqlType.equalsIgnoreCase("datetime")){  
            return "String";  
        }else if(sqlType.equalsIgnoreCase("image")){  
            return "Blod";  
        }  
        return null;  
    } 
	
	/**
	 * 将传入的帕斯卡命名的名称转换为大驼峰命名
	 * @param name table_name
	 * @return: String TableName
	 * @author: zhoumh
	 * @version: 创建时间：2017年12月22日 上午9:57:21 
	 * @Description: 根据下划线分割并转换重组
	 */
	private String initPascal(String tname){
		String[] names = tname.split("_");
		StringBuffer result = new StringBuffer();
		for (String name : names) {
			//转换首字母大写并追加到stringbuffer中
			result.append(StringUtils.capitalize(name));
		}
		return result.toString();
	}
	
	/**
	 * 从information_schema.`COLUMNS`表获取指定表名的列属性信息
	 * @param tableName 表名
	 * @return: List<Column> 列属性
	 * @author: zhoumh
	 * @version: 创建时间：2017年12月21日 下午3:48:44 
	 * @Description:
	 */
	private List<Column> getColumns(String tableName){
		//切换数据源，这种切换方式需要手动再切换回去，不再使用
//		DBContext.setDBType(DBContext.DATA_wechat);
		//获取指定表名的列信息
		String sqlstr = "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_TYPE, COLUMN_KEY, EXTRA, PRIVILEGES, COLUMN_COMMENT, GENERATION_EXPRESSION FROM information_schema.`COLUMNS` WHERE TABLE_NAME = ?";
		List<Column> columns = new ArrayList<>();
		Object[] param = new Object[]{tableName};
		try {
			columns = jdbcTemplate.query(sqlstr, param, new RowMapper<Column>(){
					@Override
					public Column mapRow(ResultSet rs, int rowNum) throws SQLException {
						return colSet(rs, rowNum);
					}
					});
//			System.out.println(JSON.toJSONString(columns));
		} catch (EmptyResultDataAccessException e) {
			e.printStackTrace();
		}
//		System.out.println(columns.size());
		return columns;
	}
	/**
	 * 列结果映射
	 * @author zhoumh
	 * @version 创建时间：2017年8月15日 上午11:36:14
	 */
	private Column colSet(ResultSet rs, int rowNum) throws SQLException {
		Column column = new Column();
		column.setTABLE_CATALOG(rs.getString("TABLE_CATALOG"));
		column.setTABLE_SCHEMA(rs.getString("TABLE_SCHEMA"));
		column.setTABLE_NAME(rs.getString("TABLE_NAME"));
		column.setCOLUMN_NAME(rs.getString("COLUMN_NAME"));
		column.setORDINAL_POSITION(rs.getInt("ORDINAL_POSITION"));
		column.setCOLUMN_DEFAULT(rs.getString("COLUMN_DEFAULT"));
		column.setIS_NULLABLE(rs.getString("IS_NULLABLE"));
		column.setDATA_TYPE(rs.getString("DATA_TYPE"));
		column.setCHARACTER_MAXIMUM_LENGTH(rs.getString("CHARACTER_MAXIMUM_LENGTH"));
		column.setCHARACTER_OCTET_LENGTH(rs.getString("CHARACTER_OCTET_LENGTH"));
		column.setNUMERIC_PRECISION(rs.getString("NUMERIC_PRECISION"));
		column.setNUMERIC_SCALE(rs.getString("NUMERIC_SCALE"));
		column.setDATETIME_PRECISION(rs.getString("DATETIME_PRECISION"));
		column.setCHARACTER_SET_NAME(rs.getString("CHARACTER_SET_NAME"));
		column.setCOLLATION_NAME(rs.getString("COLLATION_NAME"));
		column.setCOLUMN_TYPE(rs.getString("COLUMN_TYPE"));
		column.setCOLUMN_KEY(rs.getString("COLUMN_KEY"));
		column.setEXTRA(rs.getString("EXTRA"));
		column.setPRIVILEGES(rs.getString("PRIVILEGES"));
		column.setCOLUMN_COMMENT(rs.getString("COLUMN_COMMENT"));
		column.setGENERATION_EXPRESSION(rs.getString("GENERATION_EXPRESSION"));
		return column;
	}
	
}
